package com.windfone.dao.impl.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.common.Constant;
import com.windfone.dao.GiftDao;
import com.windfone.model.Gift;
import com.windfone.model.GiftUser;
import com.windfone.model.Message;

public class GiftDaoImpl  implements GiftDao{
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	String INSERT_GIFT_SQL = " INSERT INTO gift (jifen,status,account,name,imagepath,imagepath2,imagepath3,imagepath4 )  VALUES ( ?,?,?,?,?, ?,?,? ) ";
	public int addGift(final Gift bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_GIFT_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setInt(i++,bean.getJifen());
							ps.setInt(i++,bean.getStatus());
							ps.setInt(i++,bean.getAccount());
							ps.setString(i++,bean.getName());
							ps.setString(i++,bean.getImagepath());
							ps.setString(i++,bean.getImagepath2());
							ps.setString(i++,bean.getImagepath3());
							ps.setString(i++,bean.getImagepath4());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	
	String DELETE_GIFT_SQL = " DELETE FROM  gift WHERE id=? ";
	public int delGift(final Gift bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_GIFT_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String UPDATE_GIFT_SQL = "UPDATE gift SET jifen=? ,status=? ,account=? ,imagepath=?,imagepath2=?,imagepath3=?,imagepath4=?, name=? WHERE id=? ";
	public int updateGift(final Gift bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_GIFT_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getJifen());
						ps.setInt(i++,bean.getStatus());
						ps.setInt(i++,bean.getAccount());
						ps.setString(i++,bean.getImagepath());
						ps.setString(i++,bean.getImagepath2());
						ps.setString(i++,bean.getImagepath3());
						ps.setString(i++,bean.getImagepath4());
						ps.setString(i++,bean.getName());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String GET_COLOR_BYID_SQL = "  SELECT * FROM  gift WHERE   id=? ";
	public Gift getGift(final Gift bean) {
		final List<Gift> result = new ArrayList<Gift>();
		try {
			jdbcTemplate.query(
					GET_COLOR_BYID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, bean.getId());
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Gift to= new Gift();
							to.setId(rs.getInt("id"));
							to.setName(rs.getString("name"));
							to.setImagepath(rs.getString("imagepath"));
							to.setImagepath2(rs.getString("imagepath2"));
							to.setImagepath3(rs.getString("imagepath3"));
							to.setImagepath4(rs.getString("imagepath4"));
							to.setStatus(rs.getInt("status"));
							to.setJifen(rs.getInt("jifen"));
							to.setAccount(rs.getInt("account"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result.get(0);
	}

	public int countTotal() {
		return 0;
	}

	String SQL_GET_ACOUNT_GIFT=" SELECT COUNT(1) FROM  gift WHERE 1=1 ";
	public int getAllRecorderNumber(String whereCondition) {
		final List<Integer> result = new ArrayList<Integer>();
		String sql = SQL_GET_ACOUNT_GIFT + whereCondition;
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}

	
	String SQL_GET_ALL_GIFT_BY_WHERE=" SELECT * FROM gift p  WHERE 1=1 ";
 
	public List<Gift> getAllGift(int beginRecord, String whereCondition) {
		final List<Gift> result = new ArrayList<Gift>();
		String sql = SQL_GET_ALL_GIFT_BY_WHERE+ whereCondition +  "LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE) ;
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						Gift to= new Gift();
						to.setId(rs.getInt("id"));
						to.setName(rs.getString("name"));
						to.setImagepath(rs.getString("imagepath"));
						to.setImagepath2(rs.getString("imagepath2"));
						to.setImagepath3(rs.getString("imagepath3"));
						to.setImagepath4(rs.getString("imagepath4"));						
						to.setStatus(rs.getInt("status"));
						to.setJifen(rs.getInt("jifen"));
						to.setAccount(rs.getInt("account"));
						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

 
	String INSERT_MESSAGE_SQL = " INSERT INTO message (memberId,status,content,type )  VALUES ( ?,?,?,? ) ";
	public int addMessage(final Message bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_MESSAGE_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setInt(i++,bean.getMemberId());
							ps.setInt(i++,bean.getStatus());
							ps.setString(i++,bean.getContent());
							ps.setInt(i++,bean.getType());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String DELETE_MESSAGE_SQL = " DELETE FROM  message WHERE id=? ";
	public int delMessage(final Message bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_MESSAGE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	 
	String UPDATE_MESSAGE_SQL = "UPDATE message SET status=? ,content=? ,type=?   WHERE id=? AND memberId=? ";
	public int updateMessage(final Message bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				UPDATE_MESSAGE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getStatus());
						ps.setString(i++,bean.getContent());
						ps.setInt(i++,bean.getType());
						ps.setInt(i++,bean.getId());
						ps.setInt(i++,bean.getMemberId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	
	String GET_MESSAGE_SQL = "  SELECT * FROM  message WHERE   id=? ";
 
	public Message getMessage(final Message bean) {
		final List<Message> result = new ArrayList<Message>();
		try {
			jdbcTemplate.query(
					GET_MESSAGE_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, bean.getId());
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Message to= new Message();
							to.setId(rs.getInt("id"));
							to.setContent(rs.getString("content"));
							to.setStatus(rs.getInt("status"));
							to.setMemberId(rs.getInt("memberId"));
							to.setType(rs.getInt("type"));
							to.setCreateTime( rs.getTimestamp("createTime") );
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result.get(0);
	}

	
	String SQL_GET_ACOUNT_MSG=" SELECT COUNT(1) FROM  message WHERE 1=1 ";
	public int getAllMessageRecorderNumber(String whereCondition) {
		final List<Integer> result = new ArrayList<Integer>();
		String sql = SQL_GET_ACOUNT_MSG + whereCondition;
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}

	public List<Message> getAllMessage(int beginRecord, String whereCondition) {
		final List<Message> result = new ArrayList<Message>();
		String sql = "SELECT * FROM  message WHERE  1=1  "+whereCondition + " ORDER BY id Desc LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE);
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Message to= new Message();
							to.setId(rs.getInt("id"));
							to.setContent(rs.getString("content"));
							to.setStatus(rs.getInt("status"));
							to.setMemberId(rs.getInt("memberId"));
							to.setType(rs.getInt("type"));
							to.setCreateTime( rs.getTimestamp("createTime") );
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	
	String INSERT_GIFT_USER_SQL = " INSERT INTO giftuser (memberId,giftId )  VALUES ( ?,? ) ";
	public int addUserGift(final int giftId, final int userId) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_GIFT_USER_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setInt(i++,userId);
							ps.setInt(i++,giftId);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	public List<GiftUser> getAllUserAskGift(final int beginRecord, final String whereCondition) {
		final List<GiftUser> result = new ArrayList<GiftUser>();
		String sql = "SELECT  gf.*,m.name,m.loginName,g.name as  giftName ,g.account as giftNumber FROM  giftUser gf ,member m,gift g WHERE gf.memberid=m.id AND g.id=gf.giftId  "+whereCondition + " ORDER BY id Desc LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE);
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							GiftUser to= new GiftUser();
							to.setGiftNumber(rs.getInt("giftNumber"));
							to.setId(rs.getInt("id"));
							to.setStatus(rs.getInt("status"));
							to.setMemberId(rs.getInt("memberId"));
							to.setGiftId(rs.getInt("giftId"));
							to.setGiftName(rs.getString("giftName"));
							if ( rs.getString("name") ==null || "".equals(rs.getString("name") )){
								to.setMemberName(rs.getString("loginName"));
							} else 
								to.setMemberName(rs.getString("name"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	String SQL_GET_ACOUNT_USER_ASK_GIFT=" SELECT COUNT(1) FROM  giftUser WHERE 1=1 ";
	public int getAllUserAskGiftRecorderNumber(String whereCondition) {
		final List<Integer> result = new ArrayList<Integer>();
		String sql = SQL_GET_ACOUNT_USER_ASK_GIFT + whereCondition;
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}

	String UPDATE_USER_ASK_GIFT_SQL = "UPDATE giftUser SET status=?  WHERE id=? ";
	public int updateUserAskGift(final GiftUser bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_USER_ASK_GIFT_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getStatus());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String DELETE_USER_ASK_GIFT_SQL = " DELETE FROM  giftUser WHERE id=? ";
	public int deleteUserAskGift(final GiftUser bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_USER_ASK_GIFT_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

}
